package mapper

import (
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"ksd-social-api/commons/page"
	"ksd-social-api/modules/note/model"
	"ksd-social-api/modules/note/model/context"
	"ksd-social-api/modules/note/model/vo"
)

type NoteMapper struct{}

/**
 * @author feige
 * @date 2023-10-08
 * @desc 根据id查询小册信息
 */
func (mapper *NoteMapper) GetById(id uint64, systemId uint64) (note *model.Note, err error) {
	// 1: 创建一个数据连接
	db := orm.NewOrm()
	// 2: 赋予id给结构体
	dbNote := model.Note{Id: id, SystemId: systemId}
	// 3: 读取id的数据放入到结构体，进行返回
	err = db.Read(&dbNote, "id", "system_id")
	// 4: 结构返回
	return &dbNote, err
}

/**
 * @author feige
 * @date 2023-10-08
 * @desc 根据id查询小册信息，不包含内容
 */
func (mapper *NoteMapper) GetByIdTOPay(id uint64, systemId uint64) (note *model.Note, err error) {
	mysql := orm.NewOrm()
	cond := orm.NewCondition()
	cond = cond.And("id", id).And("system_id", systemId)
	qs := mysql.QueryTable("xk_note")
	dbNote := model.Note{}
	err = qs.SetCond(cond).One(&dbNote,
		"id",
		"title",
		"userid",
		"uuid",
		"img",
		"price",
		"description",
		"special_id",
		"category_id",
		"realprice",
	)
	// 4: 结构返回
	return &dbNote, err
}

///**
// * @author feige
// * @date 2023-10-12
// * @version 1.0
// * @desc 查询小册分页
// */
//func (mapper *NoteMapper) FindNoteListAndPage(ctx context.NoteSearchContext) (p *page.Page) {
//	// 1：准备容器对象，开始装载数据库数据
//	noteList := []model.Note{}
//	// 2：创建orm对象
//	mysql := orm.NewOrm()
//	cond := orm.NewCondition()
//	// 3: 开始执行sql查询
//	qs := mysql.QueryTable("xk_note")
//	// 4: 设定两个条件
//	cond = cond.And("status", 1).
//		And("is_deleted", 0).
//		And("system_id", ctx.SystemId)
//	// 5 : 判断分类是否传递如果不是""或者不是0就开始拼接条件
//	if ctx.ChannelId > 0 {
//		cond = cond.And("channel_id", ctx.ChannelId)
//	}
//	if ctx.CategoryId > 0 {
//		cond = cond.And("category_id", ctx.CategoryId)
//	}
//	if ctx.VipFlag > 0 {
//		cond = cond.And("vip_flag", ctx.VipFlag)
//	}
//	if ctx.PayFlag > 0 {
//		cond = cond.And("pay_flag", ctx.PayFlag)
//	}
//	if ctx.PushFlag > 0 {
//		cond = cond.And("push_flag", ctx.PushFlag)
//	}
//	if ctx.HotFlag > 0 {
//		cond = cond.And("hot_flag", ctx.HotFlag)
//	}
//	if ctx.NewFlag > 0 {
//		cond = cond.And("new_flag", ctx.NewFlag)
//	}
//	if ctx.Notetype > 0 {
//		cond = cond.And("notetype", ctx.Notetype)
//	}
//	// 如果用户输入的搜索关键词
//	if len(ctx.Keyword) > 0 {
//		cond1 := orm.NewCondition()
//		splits := strings.Split(ctx.Keyword, ",")
//		for _, value := range splits {
//			cond1 = cond1.Or("tags__icontains", value)
//		}
//		cond = cond.AndCond(cond1)
//	}
//
//	// 6：执行count查询
//	total, err1 := qs.SetCond(cond).Count()
//	if err1 != nil {
//		return nil
//	}
//	// 4: 重新换算分页和规则
//	page := p.Page(ctx.PageNo, ctx.PageSize, total)
//	// 5: 执行sql---count(1)---获得具体数据  10 0/ 10 10 / 10  20/  10 30
//	querySeter := qs.SetCond(cond)
//	if ctx.OrderNum == 0 { // 最新
//		querySeter = querySeter.OrderBy("-create_time")
//	} else if ctx.OrderNum == 1 { // 最热
//		querySeter = querySeter.OrderBy("-viewcount")
//	} else if ctx.OrderNum == 2 { // 最后更新
//		querySeter = querySeter.OrderBy("-update_time")
//	} else if ctx.OrderNum == 3 { // 最后更新
//		querySeter = querySeter.OrderBy("sorted")
//	}
//	_, err2 := querySeter.Limit(page.PageSize, page.CurrentPage).All(&noteList,
//		"id",
//		"title",
//		"tags",
//		"ntype",
//		"notetype",
//		"description",
//		"viewcount",
//		"create_time",
//		"update_time",
//		"comment_flag",
//		"category_id",
//		"category_name",
//		"avatar",
//		"username",
//		"status",
//		"userid",
//		"img",
//		"fav_num",
//		"sorted",
//		"comment_num",
//		"price",
//		"realprice",
//		"new_flag",
//		"hot_flag",
//		"push_flag",
//		"chapter_flag",
//		"pay_flag",
//		"channel_id",
//		"channel_name",
//		"uuid",
//		"vip_flag",
//		"pay_flag",
//		"finished",
//		"show_flag",
//		"buy_num")
//	// 6：把查询的数据放入到分页的records字段，准备返回
//	page.Records = noteList
//	// 7: 如果没有找到直接返回
//	if err2 != nil {
//		return nil
//	}
//	return page
//}

/**
 * @author feige
 * @date 2023-10-12
 * @version 1.0
 * @desc 我学习的小册
 */
func (mapper *NoteMapper) FindNoteListAndPage(ctx context.NoteSearchContext) (p *page.Page, err error) {
	// 准备容器对象，开始装载数据库数据
	noteList := []model.Note{}
	//创建orm对象
	mysql := orm.NewOrm()
	var total int64 = 0
	// 执行count查询
	sql := `select 
		id,
		title,
		tags,
		description,
		viewcount,
		create_time,
		update_time,
		comment_flag,
		is_deleted,
		avatar,
		status,
		userid,
		img,
		fav_num,
		comment_num,
		stimer,
		price,
		realprice,
		notetype,
		new_flag,
		hot_flag,
		push_flag,
		notice_msg,
		bg_img,
		uuid,
		vip_flag,
		buy_num,
		system_id,
		username,
		system_name
		pay_flag,
		url,
		special_flag,
		show_flag,
		finished,
		path,
		ntype,
		channel_id,
		channel_name,
		chapter_flag,
		category_id,
		category_name,
		sorted,
		channel_ids from xk_note where  status = 1 and is_deleted = 0 and system_id = ` + fmt.Sprintf("%d", ctx.SystemId)
	// 执行count查询
	countSql := `select count(1) from xk_note where  status = 1 and is_deleted = 0 and system_id = ` + fmt.Sprintf("%d", ctx.SystemId)

	// 5 : 判断分类是否传递如果不是""或者不是0就开始拼接条件
	if ctx.ChannelId > 0 {
		sql += " and find_in_set(" + fmt.Sprintf("%d", ctx.ChannelId) + ",channel_ids)"
		countSql += " and find_in_set(" + fmt.Sprintf("%d", ctx.ChannelId) + ",channel_ids)"
	}
	if ctx.CategoryId > 0 {
		sql += " and category_id = " + fmt.Sprintf("%d", ctx.CategoryId)
		countSql += " and category_id = " + fmt.Sprintf("%d", ctx.CategoryId)
	}
	if ctx.VipFlag > 0 {
		sql += " and vip_flag = " + fmt.Sprintf("%d", ctx.VipFlag)
		countSql += " and vip_flag = " + fmt.Sprintf("%d", ctx.VipFlag)
	}
	if ctx.PayFlag > 0 {
		sql += " and pay_flag = " + fmt.Sprintf("%d", ctx.PayFlag)
		countSql += " and pay_flag = " + fmt.Sprintf("%d", ctx.PayFlag)
	}
	if ctx.PushFlag > 0 {
		sql += " and push_flag = " + fmt.Sprintf("%d", ctx.PushFlag)
		countSql += " and push_flag = " + fmt.Sprintf("%d", ctx.PushFlag)
	}
	if ctx.HotFlag > 0 {
		sql += " and hot_flag = " + fmt.Sprintf("%d", ctx.HotFlag)
		countSql += " and hot_flag = " + fmt.Sprintf("%d", ctx.HotFlag)
	}
	if ctx.NewFlag > 0 {
		sql += " and new_flag = " + fmt.Sprintf("%d", ctx.NewFlag)
		countSql += " and new_flag = " + fmt.Sprintf("%d", ctx.NewFlag)
	}
	if ctx.Notetype > 0 {
		sql += " and notetype = " + fmt.Sprintf("%d", ctx.Notetype)
		countSql += " and notetype = " + fmt.Sprintf("%d", ctx.Notetype)
	}
	// 如果用户输入的搜索关键词
	if len(ctx.Keyword) > 0 {
		sql += " and (tags like '%" + fmt.Sprintf("%s", ctx.Keyword) + "%' or title like '%" + fmt.Sprintf("%s", ctx.Keyword) + "%')"
		countSql += " and (tags like '%" + fmt.Sprintf("%s", ctx.Keyword) + "%' or title like '%" + fmt.Sprintf("%s", ctx.Keyword) + "%')"
	}

	if ctx.OrderNum == 0 { // 最新
		sql += " order by create_time desc"
	} else if ctx.OrderNum == 1 { // 最热
		sql += " order by viewcount desc"
	} else if ctx.OrderNum == 2 { // 最后更新
		sql += " order by update_time desc"
	} else if ctx.OrderNum == 3 { // 最后更新
		sql += " order by sorted asc"
	}
	sql += " limit ?,?"

	err = mysql.Raw(countSql).QueryRow(&total)
	// 重新换算分页和规则
	page := p.Page(ctx.PageNo, ctx.PageSize, total)
	// 开始执行sql查询
	_, err2 := mysql.Raw(sql, page.CurrentPage, page.PageSize).QueryRows(&noteList)
	// 6：把查询的数据放入到分页的records字段，准备返回
	page.Records = noteList
	// 7: 如果没有找到直接返回
	if err2 != nil {
		return nil, err2
	}
	return page, nil
}

/**
 * @desc 更新浏览数
 * @author feige
 * @date 2023-11-14
 * @version 1.0
 */
func (mapper *NoteMapper) UpdateViewCount(id uint64, systemId uint64) bool {
	newOrm := orm.NewOrm()
	_, err := newOrm.Raw("update xk_note set viewcount = viewcount +1  where id = ? and system_id = ?", id, systemId).Exec()
	if err != nil {
		return false
	}
	return true
}

/**
 * @desc 更新浏览数
 * @author feige
 * @date 2023-11-14
 * @version 1.0
 */
func (mapper *NoteMapper) UpdateFavNum(id uint64, systemId uint64, num int) bool {
	newOrm := orm.NewOrm()
	_, err := newOrm.Raw("update  xk_note set fav_num = fav_num + ?  where id = ? and system_id = ?", num, systemId, id).Exec()
	if err != nil {
		return false
	}
	return true
}

/**
 * @desc 更新浏览数
 * @author feige
 * @date 2023-11-14
 * @version 1.0
 */
func (mapper *NoteMapper) UpdateLikeNum(id uint64, systemId uint64, num int) bool {
	newOrm := orm.NewOrm()
	_, err := newOrm.Raw("update  xk_note set like_num = like_num + ?  where id = ? and system_id = ?", num, id, systemId).Exec()
	if err != nil {
		return false
	}
	return true
}

/**
 * @author feige
 * @date 2023-10-12
 * @version 1.0
 * @desc 我学习的小册
 */
func (mapper *NoteMapper) FindMyNotePage(userId uint64, channelId uint64, systemId uint64, pageNo int64, pageSize int64) (p *page.Page, err error) {
	// 准备容器对象，开始装载数据库数据
	noteList := []model.Note{}
	//创建orm对象
	mysql := orm.NewOrm()

	var total int64 = 0
	// 开始执行sql查询
	err = mysql.Raw(`select count(1) from xk_note_user_buy t2,xk_note t1 where t1.id = t2.opid and t1.status = 1 and t1.is_deleted = 0 and t2.user_id = ? and t2.system_id = ? and t1.channel_id = ?`, userId, systemId, channelId).QueryRow(&total)
	if err != nil {
		return nil, err
	}
	// 重新换算分页和规则
	page := p.Page(pageNo, pageSize, total)
	// 执行count查询
	// 开始执行sql查询
	_, err2 := mysql.Raw(`
		select 
		t1.id,
		t1.title,
		t1.tags,
		t1.pan_link,
		t1.pan_pwd,
		t1.description,
		t1.viewcount,
		t1.create_time,
		t1.update_time,
		t1.comment_flag,
		t1.avatar,
		t1.username,
		t1.status,
		t1.userid,
		t1.sorted,
		t1.category_id,
		t1.category_name,
		t1.ntype,
		t1.img,
		t1.fav_num,
		t1.comment_num,
		t1.price,
		t1.realprice,
		t1.new_flag,
		t1.hot_flag,
		t1.push_flag,
		t1.chapter_flag,
		t1.channel_id,
		t1.channel_name,
		t1.uuid,
		t1.notetype,
		t1.vip_flag,
		t1.buy_num,
		t1.stimer,
		t1.finished,
		t1.pay_flag
		from xk_note_user_buy t2,xk_note t1 where t1.id = t2.opid and t1.status = 1 and t1.is_deleted = 0 and t2.user_id = ? and t2.system_id = ? and t1.channel_id = ?  order by t2.create_time desc limit ?,?`,
		userId, systemId, channelId, page.CurrentPage, page.PageSize).QueryRows(&noteList)

	// 6：把查询的数据放入到分页的records字段，准备返回
	page.Records = noteList
	// 7: 如果没有找到直接返回
	if err2 != nil {
		return nil, err2
	}
	return page, nil
}

/**
 * @author feige
 * @date 2023-10-12
 * @version 1.0
 * @desc 我浏览过的小册
 */
func (mapper *NoteMapper) FindMyNoteBroswerPage(userId uint64, channelId uint64, systemId uint64, pageNo int64, pageSize int64) (p *page.Page, err error) {
	// 准备容器对象，开始装载数据库数据
	noteList := []model.Note{}
	//创建orm对象
	mysql := orm.NewOrm()

	var total int64 = 0
	// 开始执行sql查询
	err = mysql.Raw(`select count(1) from xk_note_user_hits t2,xk_note t1 where t1.id = t2.opid and t1.status = 1 and t1.is_deleted = 0 and t2.user_id = ? and t2.system_id = ? and t1.channel_id = ?`, userId, systemId, channelId).QueryRow(&total)
	if err != nil {
		return nil, err
	}
	// 重新换算分页和规则
	page := p.Page(pageNo, pageSize, total)
	// 执行count查询
	// 开始执行sql查询
	_, err2 := mysql.Raw(`
		select 
		t1.id,
		t1.title,
		t1.tags,
		t1.username,
		t1.pan_link,
		t1.pan_pwd,
		t1.description,
		t1.viewcount,
		t1.create_time,
		t1.update_time,
		t1.comment_flag,
		t1.is_deleted,
		t1.category_id,
		t1.category_name,
		t1.avatar,
		t1.status,
		t1.userid,
		t1.img,
		t1.ntype,
		t1.fav_num,
		t1.comment_num,
		t1.price,
		t1.sorted,
		t1.realprice,
		t1.new_flag,
		t1.hot_flag,
		t1.push_flag,
		t1.channel_id,
		t1.chapter_flag,
		t1.channel_name,
		t1.notetype,
		t1.uuid,
		t1.vip_flag,
		t1.buy_num,
		t1.pay_flag,
		t1.finished,
		t1.stimer
		from xk_note_user_hits t2,xk_note t1 
		where t1.id = t2.opid and t1.status = 1 and t1.is_deleted = 0 and t2.user_id = ? and t2.system_id = ?  and t1.channel_id = ?
		order by t2.create_time desc limit ?,?`,
		userId, systemId, channelId, page.CurrentPage, page.PageSize).QueryRows(&noteList)

	// 6：把查询的数据放入到分页的records字段，准备返回
	page.Records = noteList
	// 7: 如果没有找到直接返回
	if err2 != nil {
		return nil, err2
	}
	return page, nil
}

/**
 * @desc 保存用户的小册浏览记录
 * @author feige
 * @date 2023-11-14
 * @version 1.0
 */
func (mapper *NoteMapper) UpdateNoteVideo(id uint64, title string, img string, videoid string, panLink string, panPwd string, systemId uint64) bool {
	db := orm.NewOrm()
	db.Raw("update xk_note set update_time = now(),title = ?,img = ? ,videoid = ?,pan_link = ? , pan_pwd = ? where id = ? and system_id = ?").
		SetArgs(title, img, videoid, panLink, panPwd, id, systemId).Exec()
	return false
}

func (mapper *NoteMapper) UpdateNoteVideoId(id uint64, videoid string, systemId uint64) bool {
	db := orm.NewOrm()
	db.Raw("update xk_note set update_time = now(),videoid = ?  where id = ? and system_id = ?").
		SetArgs(videoid, id, systemId).Exec()
	return false
}

/*
*保存笔记
 */
func (mapper *NoteMapper) SaveUpdateNoteVideo(note model.Note) uint64 {
	db := orm.NewOrm()
	if note.Id > 0 {
		_, _ = db.Update(&note)
		return note.Id
	} else {
		_, _ = db.Insert(&note)
		return note.Id
	}
}

/**
 * @desc 保存用户的小册浏览记录
 * @author feige
 * @date 2023-11-14
 * @version 1.0
 */
func (mapper *NoteMapper) SaveUserNoteHits(noteUserHits *model.NoteUserHits) bool {
	newOrm := orm.NewOrm()
	var notecount int
	err := newOrm.Raw("select count(1) from xk_note_user_hits where opid = ?  and user_id = ? and system_id = ?").
		SetArgs(noteUserHits.Opid, noteUserHits.UserId, noteUserHits.SystemId).QueryRow(&notecount)
	if err == nil {
		if notecount == 0 {
			orm.NewOrm().Insert(noteUserHits)
			return true
		} else {
			orm.NewOrm().Raw("update xk_note_user_hits set update_time = now() where opid = ?  and user_id = ? and system_id = ?").
				SetArgs(noteUserHits.Opid, noteUserHits.UserId, noteUserHits.SystemId).Exec()
			return true
		}
	}
	return false
}

/**
 * 删除小册记录
 * @author feige
 * @date 2024-01-13
 * @version 1.0
 * @desc
 */
func (mapper *NoteMapper) RemoveNoteHits(userId uint64, noteId uint64, systemId uint64) bool {
	_, err := orm.NewOrm().Raw("delete from xk_note_user_hits where  user_id = ? AND note_id= ? and system_id = ?", userId, noteId, systemId).Exec()
	return err == nil
}

/**
 * @author feige
 * @date 2023-10-12
 * @version 1.0
 * @desc 查询小册分页
 */
func (mapper *NoteMapper) FindPushNoteListAndPage(pageNo int64, pageSize int64, channelId uint64, systemId uint64) (p *page.Page) {
	// 1：准备容器对象，开始装载数据库数据
	noteList := []model.Note{}
	// 2：创建orm对象
	mysql := orm.NewOrm()
	cond := orm.NewCondition()
	// 3: 开始执行sql查询
	qs := mysql.QueryTable("xk_note")
	// 4: 设定两个条件
	cond = cond.And("status", 1).
		And("is_deleted", 0).
		And("push_flag", 1).
		And("system_id", systemId)
	// 5 : 判断分类是否传递如果不是""或者不是0就开始拼接条件
	if channelId > 0 {
		cond = cond.And("channel_id", channelId)
	}
	// 6：执行count查询
	total, err1 := qs.SetCond(cond).Count()
	if err1 != nil {
		return nil
	}
	// 4: 重新换算分页和规则
	page := p.Page(pageNo, pageSize, total)
	// 5: 执行sql---count(1)---获得具体数据  10 0/ 10 10 / 10  20/  10 30
	_, err2 := qs.SetCond(cond).OrderBy("-create_time").Limit(page.PageSize, page.CurrentPage).All(&noteList,
		"id",
		"title",
		"tags",
		"username",
		"notetype",
		"description",
		"viewcount",
		"create_time",
		"update_time",
		"comment_flag",
		"avatar",
		"stimer",
		"status",
		"userid",
		"img",
		"sorted",
		"fav_num",
		"comment_num",
		"price",
		"realprice",
		"new_flag",
		"hot_flag",
		"push_flag",
		"channel_id",
		"channel_name",
		"category_id",
		"category_name",
		"uuid",
		"vip_flag",
		"chapter_flag",
		"pay_flag",
		"finished",
		"channel_ids",
		"show_flag",
		"ntype",
		"buy_num")
	// 6：把查询的数据放入到分页的records字段，准备返回
	page.Records = noteList
	// 7: 如果没有找到直接返回
	if err2 != nil {
		return nil
	}
	return page
}

/**
 * 查询小册信息
 * @author feige
 * @date 2024-03-27
 * @version 1.0
 * @desc
 */
func (mapper *NoteMapper) FindNotePrevNext(categoryId uint64, systemId uint64) []*vo.NotePrevNextVo {
	// 准备容器对象，开始装载数据库数据
	noteVoList := []*vo.NotePrevNextVo{}
	//创建orm对象
	orm.NewOrm().Raw(`SELECT
		t1.id,
		t1.title,
		t1.img
	FROM
		xk_note t1
	WHERE   t1.category_id = ? AND t1.system_id = ? and t1.show_flag = 1
	ORDER BY t1.create_time desc`, categoryId, systemId).QueryRows(&noteVoList)
	return noteVoList
}

/**
 * 查询小册信息
 * @author feige
 * @date 2024-04-17
 * @version 1.0
 * @desc
 */
func (mapper *NoteMapper) FindNoteByIDS(ids []uint64) []*model.Note {
	// 准备容器对象，开始装载数据库数据
	noteList := []*model.Note{}
	//创建orm对象
	mysql := orm.NewOrm()
	qs := mysql.QueryTable("xk_note")
	cond := orm.NewCondition()
	cond = cond.
		And("status", 1).
		And("is_deleted", 0).
		And("id__in", ids)

	// 4: 设定两个条件
	_, _ = qs.SetCond(cond).OrderBy("-create_time").All(&noteList,
		"id",
		"title",
		"tags",
		"notetype",
		"stimer",
		"description",
		"viewcount",
		"create_time",
		"update_time",
		"comment_flag",
		"avatar",
		"status",
		"username",
		"userid",
		"img",
		"fav_num",
		"stimer",
		"comment_num",
		"price",
		"realprice",
		"new_flag",
		"hot_flag",
		"push_flag",
		"channel_id",
		"channel_name",
		"category_id",
		"category_name",
		"sorted",
		"uuid",
		"pay_flag",
		"chapter_flag",
		"vip_flag",
		"finished",
		"channel_ids",
		"show_flag",
		"ntype",
		"buy_num")
	return noteList
}

/**
 * @author feige
 * @date 2023-10-17
 * @version 1.0
 * @desc 统计我的消息和系统消息个数
 */
func (mapper *NoteMapper) CountNoteStudys(userId uint64, systemId uint64) []*vo.NoteStudyVo {
	vos := []*vo.NoteStudyVo{}
	_, err := orm.NewOrm().Raw(`SELECT 
		note_id ,ROUND(countnum/CONCAT(a+b) * 100,1) as percent
		FROM ( 
			SELECT count(1) countnum ,tc.note_id,(SELECT count(1) c FROM xk_note_chapter t1 WHERE t1.status = 1 AND t1.is_deleted = 0 AND t1.note_id = tc.note_id) a,(SELECT count(1) c FROM xk_note_lesson t1 WHERE t1.status = 1 AND t1.is_deleted = 0 AND t1.note_id = tc.note_id) b
			FROM xk_note_study_user_records tc WHERE tc.user_id = ? AND tc.system_id = ? AND tc.note_id in( 
				SELECT opid FROM xk_note_user_buy WHERE user_id = ? AND system_id = ?
			) GROUP BY tc.note_id
		) c`, userId, systemId, userId, systemId).QueryRows(&vos)
	if err != nil {
		return nil
	}
	return vos
}

/**
 * @author feige
 * @date 2023-10-17
 * @version 1.0
 * @desc 统计我的消息和系统消息个数
 */
func (mapper *NoteMapper) CountNoteStudyById(noteId uint64, userId uint64, systemId uint64) *vo.NoteStudyVo {
	vos := vo.NoteStudyVo{}
	err := orm.NewOrm().Raw(`SELECT 
		note_id ,ROUND(countnum/CONCAT(a+b) * 100,1) as percent
		FROM ( 
			SELECT count(1) countnum ,tc.note_id,(SELECT count(1) c FROM xk_note_chapter t1 WHERE t1.status = 1 AND t1.is_deleted = 0 AND t1.note_id = tc.note_id) a,(SELECT count(1) c FROM xk_note_lesson t1 WHERE t1.status = 1 AND t1.is_deleted = 0 AND t1.note_id = tc.note_id) b
			FROM xk_note_study_user_records tc WHERE tc.user_id = ? AND tc.system_id = ? AND tc.note_id = ? GROUP BY tc.note_id
		) c`, userId, systemId, noteId).QueryRow(&vos)
	if err != nil {
		return nil
	}
	return &vos
}

/**
 * @author feige
 * @date 2023-10-17
 * @version 1.0
 * @desc 统计我的消息和系统消息个数
 */
func (mapper *NoteMapper) CountNoteSpecialStudys(specialId uint64, userId uint64, systemId uint64) []*vo.NoteStudyVo {
	vos := []*vo.NoteStudyVo{}
	_, err := orm.NewOrm().Raw(`SELECT 
		note_id ,ROUND(countnum/CONCAT(a+b) * 100,1) as percent
		FROM ( 
			SELECT count(1) countnum ,tc.note_id,(SELECT count(1) c FROM xk_note_chapter t1 WHERE t1.status = 1 AND t1.is_deleted = 0 AND t1.note_id = tc.note_id) a,(SELECT count(1) c FROM xk_note_lesson t1 WHERE t1.status = 1 AND t1.is_deleted = 0 AND t1.note_id = tc.note_id) b
			FROM xk_note_study_user_records tc WHERE tc.user_id = ? AND tc.system_id = ? AND tc.special_id = ? GROUP BY tc.note_id
		) c`, userId, systemId, specialId).QueryRows(&vos)
	if err != nil {
		return nil
	}
	return vos
}

/**
 * @author feige
 * @date 2023-10-17
 * @version 1.0
 * @desc 统计我的消息和系统消息个数
 */
func (mapper *NoteMapper) CountNoteSpecialStudysTotal(specialId uint64, userId uint64, systemId uint64) *vo.NoteStudySpecialVo {
	vo := vo.NoteStudySpecialVo{}
	err := orm.NewOrm().Raw(`
					SELECT ? as special_id,count(1) as count ,sum(percent)/count(1) as percent from (
						SELECT 
							note_id as noteId, ROUND(countnum/CONCAT(a+b) * 100,1) as percent
						FROM ( 
							SELECT count(1) countnum ,tc.note_id,
							(SELECT count(1) c FROM xk_note_chapter t1 WHERE t1.status = 1 AND t1.is_deleted = 0 AND t1.special_id = tc.special_id) a,
							(SELECT count(1) c FROM xk_note_lesson t1 WHERE t1.status = 1 AND t1.is_deleted = 0 AND t1.special_id = tc.special_id) b
							FROM xk_note_study_user_records tc WHERE tc.user_id = ? AND tc.system_id = ? AND tc.special_id = ? GROUP BY tc.note_id
						) c 
					)cc`, specialId, userId, systemId, specialId).QueryRow(&vo)
	if err != nil {
		return nil
	}
	return &vo
}

/**
 * @author feige
 * @date 2023-10-17
 * @version 1.0
 * @desc 统计个人中心各模块的学习数量
 */
func (mapper *NoteMapper) CountNoteAll(channelId int64) []*vo.NoteMessageVo {
	vos := []*vo.NoteMessageVo{}
	_, err := orm.NewOrm().Raw(`SELECT count(1) as mnum ,"total" as ckey FROM xk_note WHERE channel_id = ? and status = 1 AND is_deleted = 0
		UNION ALL
		SELECT count(1) as mnum ,"day" as ckey FROM xk_note WHERE channel_id = ? and status = 1 AND is_deleted = 0 AND to_days(create_time) = to_days(now())
		UNION ALL
		SELECT count(1) as mnum ,"week" as ckey FROM xk_note WHERE channel_id = ? and status = 1 AND is_deleted = 0 AND DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time)
		UNION ALL
		SELECT count(1) as mnum ,"month" as ckey FROM xk_note WHERE channel_id = ? and status = 1 AND is_deleted = 0 AND DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT( CURDATE() , '%Y%m' )`, channelId, channelId, channelId, channelId).QueryRows(&vos)
	if err != nil {
		return nil
	}
	return vos
}
